﻿using Core.Framework;
using DotNetty.Framework;
using DotNetty.Framework.BaseTriggers;
using iTool.Connector.CommandOfParameter;
using Grain.Interfaces;
using iTool.ClusterComponent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace iTool.Connector.CommandWorkers
{
    [Worker("iTool Database")]
    public class DatabaseConfigurationWorker : WorkerController
    {
        private iToolClusterHostClient iCluster;

        public DatabaseConfigurationWorker(iToolClusterHostClient cluster)
        {
            this.iCluster = cluster;
        }


        [MethodRoute("Register")]
        public async Task<DoWorkResponse> Register(HandleDoWorkingContext context)
        {
            var iDatabaseConfiguration = this.iCluster.GetService<IDatabaseConfigurationService>();
            var parameter = context.Message.GetParameterModel<DatabaseConfigurationParameter>();
            await iDatabaseConfiguration.Register(parameter.Title, parameter.Connaction);
            return this.GetWorkResponse(context, "1");
        }

        [MethodRoute("Modify")]
        public async Task<DoWorkResponse> Modify(HandleDoWorkingContext context)
        {
            var iDatabaseConfiguration = this.iCluster.GetService<IDatabaseConfigurationService>();
            var parameter = context.Message.GetParameterModel<DatabaseConfigurationParameter>();
            await iDatabaseConfiguration.Modify(parameter.Title, parameter.Connaction);
            return this.GetWorkResponse(context, "1");
        }

        [MethodRoute("Remove")]
        public async Task<DoWorkResponse> Remove(HandleDoWorkingContext context)
        {
            var iDatabaseConfiguration = this.iCluster.GetService<IDatabaseConfigurationService>();
            var parameter = context.Message.GetParameterModel<DatabaseConfigurationParameter>();
            await iDatabaseConfiguration.Remove(parameter.Title);
            return this.GetWorkResponse(context, "1");
        }

        [MethodRoute("Get")]
        public async Task<DoWorkResponse> Get(HandleDoWorkingContext context)
        {
            var iDatabaseConfiguration = this.iCluster.GetService<IDatabaseConfigurationService>();
            var parameter = context.Message.GetParameterModel<DatabaseConfigurationParameter>();
            var result = await iDatabaseConfiguration.GetConnactionByTitle(parameter.Title);
            return this.GetWorkResponse(context, result);
        }

        [MethodRoute("iTool Excuted Sql", IgnoreOtherRoute = true)]
        public async Task<DoWorkResponse> ExcutedSql(HandleDoWorkingContext context)
        {
            var iDatabaseConfiguration = this.iCluster.GetService<IDatabaseConfigurationService>();
            var parameter = context.Message.GetParameterModel<DatabaseConfigurationParameter>();
            var resultOfConnaction = await iDatabaseConfiguration.GetConnactionByTitle(parameter.Title);
            var result = this.ExcutedBySql(resultOfConnaction.connationString, parameter.Sql);
            return this.GetWorkResponse(context, result);
        }

        [MethodRoute("GetList")]
        public async Task<DoWorkResponse> GetList(HandleDoWorkingContext context)
        {
            var iDatabaseConfiguration = this.iCluster.GetService<IDatabaseConfigurationService>();
            var parameter = context.Message.GetParameterModel<DatabaseConfigurationParameter>();
            var connactions = await iDatabaseConfiguration.GetConnactionList();

            List<object> list = new List<object>();

            foreach (var item in connactions)
            {
                var table = this.GetTable(item.Value);

                Dictionary<string, List<object>> tableInfo = new Dictionary<string, List<object>>();

                foreach (DataRow row in table.Rows)
                {
                    //string tableName = $"{row["name"]}.{row["tableName"]}";
                    string tableName = $"{row["tableName"]}";
                    string field = row["field"].ToString();
                    bool isKey = row["isKey"].ToString().Equals("1");

                    if (tableInfo.ContainsKey(tableName))
                    {
                        tableInfo[tableName].Add(new
                        {
                            field,
                            isKey
                        });
                    }
                    else
                    {
                        tableInfo.Add(tableName, new List<object> { new {
                                    field,
                                    isKey
                                } });
                    }
                }

                list.Add(new
                {
                    title = item.Key,
                    tableInfo
                });

            }

            return this.GetWorkResponse(context, list);
        }

        [MethodRoute("IsOpen")]
        public DoWorkResponse IsOpen(HandleDoWorkingContext context)
        {
            var parameter = context.Message.GetParameterModel<DatabaseConfigurationParameter>();
            string result = string.Empty;
            try
            {
                SqlConnection connction = new SqlConnection(parameter.Connaction);
                Task connactionTask = connction.OpenAsync();
                connactionTask.Wait(1000);

                if (!connactionTask.IsCompleted)
                {
                    throw new System.Exception("数据库连接超时");
                }

                result = "1";
            }
            catch
            {
                result = "0";
            }
            return this.GetWorkResponse(context, result);
        }

        private DataSet ExcutedBySql(string connection, string sql)
        {
            SqlConnection connction = new SqlConnection(connection);
            connction.Open();

            try
            {
                SqlDataAdapter command = new SqlDataAdapter(sql, connction);

                DataSet ds = new DataSet();
                command.Fill(ds);
                return ds;
            }
            catch //(System.Exception ex)
            {
                throw;
            }
            finally
            {
                connction.Close();
            }
        }

        private DataTable GetTable(string connection)
        {
            SqlConnection connction = null;
            try
            {
                connction = new SqlConnection(connection);
                Task connactionTask = connction.OpenAsync();
                connactionTask.Wait(1000);
                if (!connactionTask.IsCompleted)
                {
                    throw new System.Exception("数据库连接超时");
                }
                string sql = @"SELECT
	                        i.name ,
                         (case when a.colorder=1 then d.name else d.name end) tableName,  
                         a.name field,
                         (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end) isKey

                         FROM  syscolumns a 

                         left join systypes b on a.xtype=b.xusertype  
                         inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
                         left join syscomments e on a.cdefault=e.id  
                         left join sys.objects h on a.id= h.object_id
                         left join sys.schemas i on h.schema_id=i.schema_id

                        where b.name is not null
                         order by a.id,a.colorder";
                DataTable dataTable = new DataTable();
                SqlCommand command = new SqlCommand(sql, connction);
                var dataReader = command.ExecuteReader();
                dataTable.Load(dataReader);
                return dataTable;
            }
            catch
            {
                return new DataTable();
            }
            finally
            {
                connction?.Close();
            }
        }

    }
}
